package Dao;

import UnderAccessDBclass.Druid_utils;
import bean.Student;
import UnderAccessDBclass.jdbcUtils;

import java.sql.Date;
import java.util.ArrayList;

public class StudentDao {
    public ArrayList<Student> selectAll() {
        ArrayList<Student> studentList = new ArrayList<>();
        String sql = "SELECT sno,sname,sex,birthday,dname FROM student s,dep d WHERE s.`dno`=d.`dno`";
        ArrayList<Object[]> list = Druid_utils.executeQuery(sql, null);
        for (Object[] objects : list) {
            Student student = new Student();
            student.setSno((String) objects[0]);
            student.setSname((String) objects[1]);
            student.setSex((String) objects[2]);
            if (objects[3] != null) {
                student.setBirthday(objects[3].toString());
            }
            student.setDname((String) objects[4]);
            studentList.add(student);
        }
        return studentList;
    }

    public Student selectBySno(String sno) {
        String sql = "SELECT sno,sname,sex,birthday,dname FROM student s,dep d WHERE s.dno=d.dno AND s.sno=?";
        Object[] objs = Druid_utils.executeQuery(sql, new Object[]{sno}).get(0);
        Student student = new Student();
        student.setSno((String) objs[0]);
        student.setSname((String) objs[1]);
        student.setSex((String) objs[2]);
        if (objs[3] != null) {
            student.setBirthday(objs[3].toString());
        }
        student.setDname((String) objs[4]);

        return student;
    }

    public ArrayList<Student> selectBySname(String sname) {
        ArrayList<Student> studentList = new ArrayList<>();
        String sql = "SELECT sno,sname,sex,birthday,dname FROM student s,dep d WHERE s.dno=d.dno AND s.sname=?;";
        ArrayList<Object[]> list = Druid_utils.executeQuery(sql, new Object[]{sname});
        for (Object[] objects : list) {
            Student student = new Student();
            student.setSno((String) objects[0]);
            student.setSname((String) objects[1]);
            student.setSex((String) objects[2]);
            if (objects[3] != null) {
                student.setBirthday(objects[3].toString());
            }
            student.setDname((String) objects[4]);
            studentList.add(student);
        }
        return studentList;
    }

    public ArrayList<Student> selectByDno(String dno) {
        ArrayList<Student> studentList = new ArrayList<>();
        String sql = "SELECT * FROM student s,dep d WHERE s.dno=d.dno AND s.dno=?";
        ArrayList<Object[]> list = Druid_utils.executeQuery(sql, new Object[]{dno});
        for (Object[] objects : list) {
            Student student = new Student();
            student.setSno((String) objects[0]);
            student.setSname((String) objects[1]);
            student.setSex((String) objects[2]);
            if (objects[3] != null) {
                student.setBirthday(objects[3].toString());
            }
            student.setDname((String) objects[4]);
            studentList.add(student);
        }
        return studentList;
    }

    public ArrayList<Student> delete(String sno) {
        String sql = "DELETE FROM student WHERE sno=?";
        if (jdbcUtils.executeUpdate(sql, new Object[]{sno})) {
            System.out.println("删除成功");
        }
        return selectAll();
    }

    public ArrayList<Student> batchDelete(String[] sno_list) {
        StringBuilder sql_builder = new StringBuilder("DELETE FROM student WHERE sno IN (");
        Object[] params = new Object[sno_list.length];
        for (int i = 0; i < sno_list.length; i++) {
            params[i] = sno_list[i];
            if (i == sno_list.length - 1) {
                sql_builder.append("?);");
            } else {
                sql_builder.append("?,");
            }
        }
        String sql = sql_builder.toString();
        if (jdbcUtils.executeUpdate(sql, params)) {
            System.out.println("批量删除成功");
        }
        return selectAll();
    }

    public ArrayList<Student> insert(String sno, String sname, String sex, String birthday, String dno) {
        String sql = "INSERT INTO student VALUE (?,?,?,?,?)";
        Object bir = birthday;
        if (bir != null) {
            bir = Date.valueOf(birthday);
        }
        Object[] params = new Object[]{sno, sname, sex, bir, dno};
        if (Druid_utils.executeUpdate(sql, params)) {
            System.out.println("插入成功");
        }
        return selectAll();
    }

    public ArrayList<Student> update(String oldSno, String sno, String sname, String sex, String birthday, String dno) {
        String sql = "UPDATE student SET sno=?,sname=?,sex=?,birthday=?,dno=? WHERE sno=?";
        Object bir = birthday;
        if (bir != null) {
            bir = Date.valueOf(birthday);
        }
        Object[] params = new Object[]{sno, sname, sex, bir, dno, oldSno};
        if (Druid_utils.executeUpdate(sql, params)) {
            System.out.println("修改成功");
        }
        return selectAll();
    }

    public static void main(String[] args) {
        StudentDao studentDao = new StudentDao();
        ArrayList<Student> studentList = studentDao.selectByDno("d06");
        System.out.println(studentList.size());
        for (Student student : studentList) {
            System.out.println(student);
        }
    }
}
